package com.coded2.mobileposto.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import com.coded2.infra.comum.ComumDAO;
import com.coded2.infra.interceptor.transactional.Transaction;
import com.coded2.mobileposto.model.Combustivel;
import com.coded2.mobileposto.model.Posto;
import com.coded2.mobileposto.model.Servico;

public class PostoDAO extends ComumDAO<Posto> {

	private static final long serialVersionUID = 1L;

	@Override
	protected List<Predicate> searchConditions(Posto obj,
			CriteriaBuilder builder, Root<Posto> root) {
		List<Predicate> conditions = new ArrayList<Predicate>();
		return conditions;
	}

	@Transaction
	public void deleteCombustiveis(Posto posto) { 
		String sql="DELETE FROM `mobile_posto_admin`.`posto_combustivel` "
				 + "WHERE  `posto_combustivel`.`id_posto` = ?;";
		Query query = em.createNativeQuery(sql);
		int index=1;
		query.setParameter(index++, posto.getIdPosto());
		query.executeUpdate();
	}

	public Collection<Combustivel> searchByCombustiveis() {
		String sql="SELECT `combustivel`.`id_combustivel`,"
				+ "`combustivel`.`no_combustivel` "
				+ "FROM `mobile_posto_admin`.`combustivel`;";
		Query query = em.createNativeQuery(sql,Combustivel.class);
		return query.getResultList();
	}

	@Transaction
	public void insertServico(Servico _ser, int idPosto) {
		String sql="INSERT INTO `mobile_posto_admin`.`servico_posto` "
				 + "(`id_servico`, `id_posto`) "
				 + "VALUES (?, ?);";
		Query query = em.createNativeQuery(sql);
		int index=1;
		query.setParameter(index++, _ser.getIdServico());
		query.setParameter(index++, idPosto);
		query.executeUpdate();
	}

	public List<Servico> searchByServicos(Posto posto) {
		String sql="SELECT sp.id_servico, sp.id_posto, sr.no_servico "
				 + "FROM mobile_posto_admin.servico_posto as sp "
				 + "inner join mobile_posto_admin.servico sr on (sp.id_servico= sr.id_servico)"
				 + "WHERE sp.id_posto = ?;";
		Query query = em.createNativeQuery(sql,Servico.class);
		int index=1;
		query.setParameter(index++, posto.getIdPosto());
		return query.getResultList();
	}

	@Transaction
	public void deleteServicos(Posto posto) {
		String sql="DELETE FROM `mobile_posto_admin`.`servico_posto`"
				 + "WHERE  `servico_posto`.`id_posto` = ?;";
		Query query = em.createNativeQuery(sql);
		int index=1;
		query.setParameter(index++, posto.getIdPosto());
		query.executeUpdate();
	}
	
}
